package com.central.apps.controller;
import org.apache.poi.ss.usermodel.Cell;
import com.central.app.service.HandlerService;
import com.central.common.exception.service.ServiceException;
import com.central.common.util.StringUtil;
import com.central.common.util.ToolUtil;
import com.central.common.web.Result;
import io.swagger.annotations.Api;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/*
 * 合同初始化导入
 * */
@Slf4j
@Api(tags = "合同管理初始化数据导入")
@CrossOrigin
@RestController
@RequestMapping("/upload")
public class ConUploadController {

    @Autowired
    private HandlerService handlerService;


    @PostMapping("/conapplicationInfo")
    @Transactional(rollbackFor = Exception.class)
    public Result conapplicationInfo(@RequestParam("file") MultipartFile myfile) throws Exception {

            /*
            （申请人填写中文，查询系统中申请人的编号，申请部门，申请单位，课题号，关联合同
              下拉框选择字段：合同类别，合同类型，是否签订合同，是否框架合同，采购方式，级别，合同密级，申请信息是否准确）
            * 1.获取sheet页数据
            * 2.获取每一行的数据，遍历字段，根据系统设置的必填项，
            * 验证必填项是否有值；
            * 验证字段值的类型是否正确（数字型，日期型）
            * 验证是否有重复数据：合同编号唯一
              验证填写的系统中的数据，系统中是否存在如 申请人，申请部门等。如存在一些下拉框字段，直接限定选择范围
            * 如数据有问题，提示出具体的哪一行哪一列数据不对
            * 3.获取子表数据，同上一样验证数据准确性，给出准确的提示
            * */

        Workbook workbook = null;
        String fileName = myfile.getOriginalFilename();

        workbook = new XSSFWorkbook(myfile.getInputStream());
        //获取excel的Sheet页
        Sheet sheet1 = workbook.getSheet("合同申请主数据");
        if(sheet1!=null){
            String[] myArray = {"id", "conid", "iscollcon", "contracttype", "isconsign", "isframecon","applicantEntity_name", "conname", "totalprice", "protype", "level", "applytime", "conlevel", "subjectno", "constatus", "accuracy"};
            String[] myArray2 = {"主键id", "合同序号", "合同类别", "合同类型", "是否签订合同","是否框架合同", "申请人", "合同名称", "合同金额", "采购方式", "级别", "申请日期", "合同密级", "课题号", "合同状态", "申请信息是否准确"};
            String[] myArray3={"主键id","合同序号","合同金额"};//数字型
            String[] myArray4={"申请日期"};//日期型
            List<String[]> lists = new ArrayList<>();
            lists.add(myArray);
            lists.add(myArray2);
            lists.add(myArray3);
            lists.add(myArray4);
            setInfo(sheet1,lists,"gq_conapplication","conapplicationApp","applicationnum",null,null);
        }

        Sheet sheet2 = workbook.getSheet("对方单位名称");
        if(sheet2!=null){
            String[] myArry01 = {"conid","isdirectories","companyname"};
            String[] myArry02 = {"合同序号","是否名录内","对方单位名称"};
            List<String[]> list2 = new ArrayList<>();
            list2.add(myArry01);
            list2.add(myArry02);
            setInfo(sheet2,list2,"gq_coordinationinfo",null,null,null,null);
        }


        Sheet sheet3 = workbook.getSheet("订单明细");
        if(sheet3!=null){
            String[] myArry001 = {"conpid","conid","itemnum"};
            String[] myArry002 = {"合同申请主数据主键id","合同序号","物料编码"};
            List<String[]> list3 = new ArrayList<>();
            list3.add(myArry001);
            list3.add(myArry002);
            List<String> listzhu = new ArrayList<>();
            listzhu.add("gq_conapplication");
            listzhu.add("conpid");//关联主表主键id的字段名
            listzhu.add("conid");//关联主表的非主键id字段名
            List<String> extendList = new ArrayList<>();
            extendList.add("gq_item");//物料主数据表
            extendList.add("itemnum");//物料编码字段
            setInfo(sheet3,list3,"gq_conpoline",null,null,listzhu,extendList);
        }


        /*
        * 直接读取excel中的数据 合同签订
        * */
        Sheet sheet4 = workbook.getSheet("合同签订主数据");
        if(sheet4!=null){
            List<String[]> list4 = setlist(sheet4);
            setInfo(sheet4,list4,"gq_consigns","consignsApp","consignsnum",null,null);
        }
        /*
        * 合同节点
        * */
        Sheet sheet5 = workbook.getSheet("合同节点");
        if(sheet5!=null){
            List<String[]> list5 = setlist(sheet5);
            List<String> listzhu = new ArrayList<>();
            listzhu.add("gq_consigns");
            listzhu.add("cid");//关联主表主键id的字段名
            listzhu.add("concode");//关联主表的非主键id字段名
            setInfo(sheet5,list5,"gq_connode","consignsApp","nodecode",listzhu,null);
        }
        /*
        * 物料数据
        * */
        Sheet sheet6 = workbook.getSheet("物料数据");
        if(sheet6!=null){
            List<String[]> list6 = setlist(sheet6);
            setInfo(sheet6,list6,"gq_poline",workbook);
        }

        return Result.succeed("导入成功");
    }
    private List<String[]> setlist(Sheet sheet){
        List<String[]> list= new ArrayList<>();
        for(int i=0;i<2;i++){
            Row row = sheet.getRow(i);
            int columns = row.getLastCellNum();
            String[] column_values = new String[columns];
            for (int j = 0; j < columns; j++) {
                Cell cell = row.getCell(j);
                column_values[j] = cell.getStringCellValue();
            }
            list.add(column_values);
        }
        return list;
    }

    private void setInfo(Sheet sheet1 ,List<String[]> list,String table,String app,String lie,List<String> zhulist,List<String> extendList){
        //判断excel一共多少行
        int rows = sheet1.getLastRowNum();
        for (int i = 2; i <= rows; i++) {
            //读取左上端单元格
            Row row = sheet1.getRow(i);
            //行不为空
            if (row != null) {
                int line = i + 1;
                Map<String, Object> map=new HashMap<>();
                for (int m = 0; m<list.get(0).length; m++ ) {
                    String value = getCellValue(row, m).toString().trim();//主键
                    String key = list.get(0)[m];
                    //String intKey = myArray3[m];
                    if( ToolUtil.isEmpty(value)){
                        throw new ServiceException(sheet1.getSheetName() +"第" + line + "行的"+list.get(1)[m]+"不能为空");
                    }
                    if(list.size()>=3&&Arrays.asList(list.get(2)).contains(key)&& !StringUtil.isNumeric(value)){
                        throw new ServiceException(sheet1.getSheetName() +"第" + line + "行的"+list.get(1)[m]+"为数字型");
                    }
                    if(list.size()>=4&& Arrays.asList(list.get(3)).contains(key)&& !StringUtil.isNumeric(value)){
                        throw new ServiceException(sheet1.getSheetName() +"第" + line + "行的"+list.get(1)[m]+"为必须是日期型");
                    }
                    map.put(key,value);

                    //获取zhulist.get(2)  对应的值
                    if(CollectionUtils.isNotEmpty(zhulist)&&key.equals(zhulist.get(2))){
                        String conid = zhulist.get(2);
                        String conpid = zhulist.get(1);
                        String tables = zhulist.get(0);
                        List<Map<String,Object>> list1 = handlerService.findBySql("select id from "+tables+" where "+conid+" ='"+value+"'");
                        if(CollectionUtils.isNotEmpty(list1)){
                            map.put(conpid,list1.get(0).get("id"));
                        }
                    }
                    //获取物料主数据编号，查询相应数据进行添加
                    if(CollectionUtils.isNotEmpty(extendList)&&key.equals(extendList.get(1))){
                        String itemnum = extendList.get(1);
                        String tables = extendList.get(0);
                        try {
                            Map<String,Object> map1 = handlerService.findOneBySql("select * from "+tables+" where "+key+" ='"+value+"'");
                            map.putAll(map1);
                            map.put("itemdesc",map1.get("description"));
                            map.put("orderunit",map1.get("unit"));
                            map.put("itemid",map1.get("id"));
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }

                }


                try {
                    if(ToolUtil.isNotEmpty(app)){
                        Map<String,Object> map1 = new HashMap<>();
                        handlerService.addBizNumNew(app,table,null,map1);
                        map.put(lie,map1.get(lie));
                    }
                    map.put("status","新建");
                    map.put("maincon","");
                    handlerService.insert(table,map);
                    if("gq_conapplication".equals(table)){
                        handlerService.insert("gq_conaccount",map);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }


    }
    private void setInfo(Sheet sheet1 ,List<String[]> list,String table,Workbook workbook){
        //判断excel一共多少行
        int rows = sheet1.getLastRowNum();
        for (int i = 2; i <= rows; i++) {
            //读取左上端单元格
            Row row = sheet1.getRow(i);
            //行不为空
            if (row != null) {
                int line = i + 1;
                Map<String, Object> map=new HashMap<>();
                for (int m = 0; m<list.get(0).length; m++ ) {
                    String value = getCellValue(row, m,workbook).toString().trim();//主键
                    String key = list.get(0)[m];
                    //String intKey = myArray3[m];
                    if( ToolUtil.isEmpty(value)){
                        throw new ServiceException(sheet1.getSheetName() +"第" + line + "行的"+list.get(1)[m]+"不能为空");
                    }
                    if(list.size()>=3&&Arrays.asList(list.get(2)).contains(key)&& !StringUtil.isNumeric(value)){
                        throw new ServiceException(sheet1.getSheetName() +"第" + line + "行的"+list.get(1)[m]+"为数字型");
                    }
                    if(list.size()>=4&& Arrays.asList(list.get(3)).contains(key)&& !StringUtil.isNumeric(value)){
                        throw new ServiceException(sheet1.getSheetName() +"第" + line + "行的"+list.get(1)[m]+"为必须是日期型");
                    }
                    map.put(key,value);



                }


                try {
                    map.put("status","新建");
                    map.put("maincon","");
                    handlerService.insert(table,map);
                    if("gq_conapplication".equals(table)){
                        handlerService.insert("gq_conaccount",map);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }


    }
    private void setInfo(Sheet sheet1 ,List<String[]> list,String table){

        setInfo(sheet1,list,table,null,null,null,null);

    }
    /**
     * 获取Cell内容
     */
    public Object getCellValue(Row row, int column) {
        return getCellValue( row,  column,null);
    }


    public Object getCellValue(Row row, int column, Workbook workbook) {
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (cell != null) {

                if (cell.getCellTypeEnum() == CellType.NUMERIC) {
                    val = cell.getNumericCellValue();
                    DecimalFormat df = new DecimalFormat("#.#########");
                    String cellValueStr = String.valueOf(val);
                    val = df.format(Double.valueOf(cellValueStr));
                } else if (cell.getCellTypeEnum() == CellType.STRING) {
                    val = cell.getStringCellValue();
                } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellTypeEnum() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }

                if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
                    Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
                    val = dateFormat.format(date);
                }
                if (cell.getCellType() == CellType.FORMULA&&workbook!=null) {
                    FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

                    CellValue cellValue = evaluator.evaluate(cell);
                    val = cellValue.getNumberValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }
}
